﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Collections;
using System.Data;
using System.IO;
using System.Web;
using System.Globalization;
using System.Configuration;
namespace SavoryDL
{
    public class Savory_DBServices
    {

        public static SqlCommand GetCommand(SqlConnection conn, string cmdText, CommandType cmdtype,
                                            ArrayList parameters)
        {
            var cmd = new SqlCommand { Connection = conn };
            try
            {
                if (cmdText != "")
                {
                    cmd.CommandText = cmdText;
                }

                cmd.CommandType = !cmdtype.Equals(null) ? cmdtype : CommandType.Text;


                if (parameters != null)
                // check if parameter collection is passed, then add those into Command.Parameter Collection
                {
                    foreach (SqlParameter sqlparam in parameters)
                    {
                        cmd.Parameters.Add(sqlparam);
                    }
                }
            }
            catch (Exception ex)
            {
                var we = new WriteError();
                we.WriteErrorLog(ex);
                throw;
            }
            return cmd; // returns command object
        }

        public static DataSet GetDataSet(string cmdText, CommandType cmdType, ArrayList sqlParams)
        {
            var ds = new DataSet();
            SqlCommand cmd;
            try
            {
                using (var sqlConn = ConnectionObject)
                {

                    cmd = GetCommand(sqlConn, cmdText, CommandType.StoredProcedure, sqlParams);
                    cmd.CommandTimeout = 0; //time in sec's. 0 = no timeout
                    var adp = new SqlDataAdapter(cmd);
                    adp.Fill(ds);
                    adp.Dispose();
                    cmd.Dispose();
                }
            }
            catch (Exception exp)
            {
                var we = new WriteError();
                we.WriteErrorLog(exp);
                throw;
            }

            return ds;
        }
        public static int ExecuteQuery(string cmdText, CommandType cmdType, ArrayList sqlParams)
        {
            int recordCount;
            SqlCommand sqLcomm;

            try
            {
                using (var sqlConn = ConnectionObject)
                {
                    sqLcomm = GetCommand(sqlConn, cmdText, cmdType, sqlParams);

                    sqlConn.Open();
                    recordCount = sqLcomm.ExecuteNonQuery();
                    sqlConn.Close();

                }
            }
            catch (Exception exp)
            {
                var we = new WriteError();
                we.WriteErrorLog(exp);
                throw;
            }

            return recordCount;
        }

        public static object GetScalarData(string cmdText, CommandType cmdType, ArrayList sqlParams)
        {
            object retObj;
            SqlCommand sqLcomm;
            try
            {
                using (var sqlConn = ConnectionObject)
                {
                    sqLcomm = GetCommand(sqlConn, cmdText, cmdType, sqlParams);

                    sqlConn.Open();
                    retObj = sqLcomm.ExecuteScalar();
                    sqlConn.Close();
                }
            }
            catch (Exception exp)
            {
                var we = new WriteError();
                we.WriteErrorLog(exp);
                throw;
            }

            return retObj;
        }

        public static object GetScalarData(string cmdText)
        {
            object retObj;
            //ConnectionObject conObject = OpenConnection();
            SqlCommand sqLcomm;

            try
            {
                using (var sqlConn = ConnectionObject)
                {
                    sqLcomm = GetCommand(sqlConn, cmdText, CommandType.Text, null);

                    sqlConn.Open();
                    retObj = sqLcomm.ExecuteScalar();
                    sqlConn.Close();

                }
            }
            catch (Exception ex)
            {
                var we = new WriteError();
                we.WriteErrorLog(ex);
                throw;
            }

            return retObj;
        }

        public enum CommandTypes
        {
            TableDirect = 0,
            Text = 1
            // Odbc - v1.1
        }

        //commandTypes
        public enum ReaderTypes
        {
            DataReader = 0, //Datareader
            DataScalar = 1//datareader scalar

        }

        public enum ReturnTypes
        {
            None = 0,         // Datareader
            ReturnValue = 1 // Datareader scalar
        }

        public static SqlCommand GetSqlCommand(int dbCommandType, string dbCommand)
        {
            using (var sqLcomm = new SqlCommand())
            {
                try
                {
                    //This If statement exists because the CreateCommand is not implemented for SQL

                    sqLcomm.CommandText = dbCommand;
                    switch (dbCommandType)
                    {
                        case (int)CommandTypes.TableDirect:
                            sqLcomm.CommandType = CommandType.TableDirect;
                            break;
                        case (int)CommandTypes.Text:
                            sqLcomm.CommandType = CommandType.Text;
                            break;
                    }
                }
                catch (Exception ex)
                {
                    var we = new WriteError();
                    we.WriteErrorLog(ex);
                    throw;
                }
                //finally { SQLcomm = null; }
                return sqLcomm;
            }
        }

        public SqlDataReader GetDataReader(int dbCommandType, string dbCommand, int readerType)
        {
            SqlDataReader sqLdtr = null;
            SqlCommand sqLcomm;
            try
            {

                var sqlConn = ConnectionObject;
                sqLcomm = GetSqlCommand(dbCommandType, dbCommand);
                sqLcomm.Connection = sqlConn;
                sqlConn.Open();

                switch (readerType)
                {
                    case (int)ReaderTypes.DataReader:
                        sqLdtr = sqLcomm.ExecuteReader(CommandBehavior.CloseConnection);
                        break;
                    case (int)ReaderTypes.DataScalar:
                        sqLdtr = (SqlDataReader)sqLcomm.ExecuteScalar();
                        break;
                }
                return sqLdtr;
            }
            catch (Exception ex)
            {
                var we = new WriteError();
                we.WriteErrorLog(ex);
                throw;
            }
        }

        public void PopulateCheckBoxList(ref System.Web.UI.WebControls.CheckBoxList cbl, string sSql)
        {
            if (sSql == null) throw new ArgumentNullException("sSql");

            try
            {
                //Create DataReader 
                using (var rdrData = GetDataReader((int)CommandTypes.Text, sSql, (int)ReaderTypes.DataReader))
                {

                    //Binding CheckBoxList
                    cbl.DataTextField = "Data_Text";
                    cbl.DataValueField = "Data_Value";
                    cbl.DataSource = rdrData;
                    cbl.DataBind();
                }
            }
            catch (Exception ex)
            {
                var we = new WriteError();
                we.WriteErrorLog(ex);
                throw;
            }
        }

        public void PopulateDdl(ref System.Web.UI.WebControls.DropDownList ddList, string sSql)
        {
            if (sSql == null) throw new ArgumentNullException("sSql");
            try
            {
                //Create DataReader 
                using (var rdrData = GetDataReader((int)CommandTypes.Text, sSql, (int)ReaderTypes.DataReader))
                {

                    //Binding DropDownlist 
                    ddList.DataTextField = "Data_Text";
                    ddList.DataValueField = "Data_Value";
                    ddList.DataSource = rdrData;
                    ddList.DataBind();
                }
            }
            catch (Exception ex)
            {
                var we = new WriteError();
                we.WriteErrorLog(ex);
                throw;
            }
        }

        public static SqlConnection ConnectionObject
        {
            get
            {
                try
                {
                    var sqlConn = new SqlConnection();
                    sqlConn.ConnectionString = "Data Source=199.79.62.22;Initial Catalog=realestate_A;Persist Security Info=True;User ID=realstate;Password=password123$123";
                    return sqlConn;
                }
                catch (Exception ex)
                {
                    var we = new WriteError();
                    we.WriteErrorLog(ex);
                }
                return null;
            }
        }

        public static int DataNonQuery(int dbCommandType, string dbCommand, int dbReturnType)
        {
            //On Error Resume Next
            var sqLcomm = new SqlCommand();
            try
            {
                using (var sqlConn = ConnectionObject)
                {
                    sqLcomm = GetSqlCommand(dbCommandType, dbCommand);
                    sqLcomm.Connection = sqlConn;

                    if (dbReturnType == (int)(ReturnTypes.ReturnValue))
                    {
                        var parmReturnValue = new SqlParameter
                        {
                            ParameterName = "@ReturnValue",
                            SqlDbType = SqlDbType.Int,
                            Direction = ParameterDirection.ReturnValue
                        };
                        sqLcomm.Parameters.Add(parmReturnValue);

                        sqlConn.Open();
                        sqLcomm.ExecuteNonQuery();

                        var iReturn = Convert.ToInt32(sqLcomm.Parameters["@ReturnValue"].Value);
                        return iReturn;
                    }
                    else
                    {
                        sqlConn.Open();
                        var i = sqLcomm.ExecuteNonQuery();
                        return i;
                    }
                }
            }
            catch (Exception ex)
            {
                var we = new WriteError();
                we.WriteErrorLog(ex);
                throw;
            }
            finally
            {
                if ((sqLcomm != null))
                    sqLcomm.Dispose();
            }
        }

        public class WriteError
        {
            public void WriteErrorLog(Exception ex)
            {
                try
                {
                    if (!Directory.Exists(HttpContext.Current.Server.MapPath("\\research")))
                    {
                        Directory.CreateDirectory("\\research");
                    }
                    var sPath = HttpContext.Current.Server.MapPath("\\research");

                    sPath += "\\Log" + DateTime.Today.ToString("MM-dd-yyyyy") + ".txt";

                    if (!File.Exists(sPath))
                    {
                        File.Create(sPath).Close();
                    }

                    using (var w = File.AppendText(sPath))
                    {
                        w.Write(Environment.NewLine);
                        w.Write("{0}", DateTime.Now.ToString(CultureInfo.InvariantCulture) + ",");
                        var err = "Error in: " + HttpContext.Current.Request.Url +
                                  "," + ex.Message + "," + ex.Source + "," + ex.StackTrace;
                        w.Write(err);
                        w.Flush();
                        w.Close();
                    }
                }
                catch (Exception e)
                {

                }
            }
        }

    }
}

